﻿using System;
using System.Collections.Generic;
using Demo.IDAL;
using Demo.Model;
using System.Data;
using System.Data.SqlClient;

namespace Demo.SQLServerDAL
{
    /// <summary>
    /// SQLServer操作新闻类
    /// </summary>
    public class NewsService : INewsService
    {
        #region SQL参数
        readonly static String Parm_NewsID = "@NewsID";
        readonly static String Parm_newsTitle = "@newsTitle";
        readonly static String Parm_newsContext = "@newsContext";
        readonly static String Parm_CreateTime = "@CreateTime";
        #endregion

        #region SQL语句
        readonly static String select_News_All = "select NewsID,NewsTitle,NewsContext,CreateTime from TB_news";
        readonly static String select_News_ByID = "select NewsID,NewsTitle,NewsContext,CreateTime from TB_news where NewsID=@NewsID";
        readonly static String insert_News_One = "insert into TB_news(newsTitle,newsContext,CreateTime)values (@newsTitle,@newsContext,@CreateTime)";
        readonly static String delete_News_ByID = "delete from TB_news where  NewsID=@NewsID";
        readonly static String update_News_ByID = "update TB_news set  newsTitle=@newsTitle,newsContext=@newsContext ,CreateTime=@CreateTime where NewsID=@NewsID";
        #endregion

        #region CRUD方法

        /// <summary>
        /// 根据ID，获得指定的新闻数据
        /// </summary>
        /// <param name="ID">新闻ID</param>
        /// <returns>NewsModel</returns>
        public NewsModel GetNewsByID(Int32 ID)
        {
            SqlParameter[] PM = { new SqlParameter(Parm_NewsID, ID) };
            NewsModel news = null;
            SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, select_News_ByID, PM);
            if (sdr.Read())
            {
                news = new NewsModel()
                {
                    NewsID = sdr.GetInt32(0),
                    NewsTitle = sdr.GetString(1),
                    NewsContext = sdr.GetString(2),
                    NewsDate = sdr.GetDateTime(3)
                };
            }
            sdr.Close();
            sdr.Dispose();
            return news;
        }

        /// <summary>
        /// 获得所有新闻内容
        /// </summary>
        /// <returns>  List<NewsModel> </returns>
        public List<NewsModel> GetAllNews()
        {
            List<NewsModel> listNews = new List<NewsModel>();
            SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, select_News_All);
            while (sdr.Read())
            {
                NewsModel news = new NewsModel()
                {
                    NewsID = sdr.GetInt32(0),
                    NewsTitle = sdr.GetString(1),
                    NewsContext = sdr.GetString(2),
                    NewsDate = sdr.GetDateTime(3)
                };
                listNews.Add(news);
            }
            sdr.Close();
            sdr.Dispose();
            return listNews;
        }

        /// <summary>
        /// 添加一条新闻
        /// </summary>
        /// <param name="news">待添加的新闻对象</param>
        /// <returns>添加成功，返回ture；否则返回false</returns>
        public bool AddNews(NewsModel news)
        {
            SqlParameter[] PM = { 
                                    new SqlParameter(Parm_newsTitle,news.NewsTitle),
                                    new SqlParameter(Parm_newsContext,news.NewsContext),
                                    new SqlParameter(Parm_CreateTime,news.NewsDate)
                                };
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, insert_News_One, PM) > 0 ? true : false;
        }

        /// <summary>
        /// 更新指定新闻数据
        /// </summary>
        /// <param name="news">待更新的新闻对象(包括该条新闻的ID)</param>
        /// <returns>更新成功，返回ture；否则返回false</returns>
        public bool ModityNewsByID(NewsModel news)
        {
            SqlParameter[] PM = { 
                                    new SqlParameter(Parm_newsTitle,news.NewsTitle),
                                    new SqlParameter(Parm_newsContext,news.NewsContext),
                                    new SqlParameter(Parm_CreateTime,news.NewsDate),
                                    new SqlParameter(Parm_NewsID,news.NewsID)
                                };
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, update_News_ByID, PM) > 0 ? true : false;
        }

        /// <summary>
        ///  删除某条指定的新闻
        /// </summary>
        /// <param name="ID">待删除新闻的ID</param>
        /// <returns>删除成功，返回ture；否则返回false</returns>
        public bool RemoveNewsByID(Int32 newsID)
        {
            SqlParameter[] PM = { 
                                    new SqlParameter(Parm_NewsID,newsID)
                                };
            return SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, delete_News_ByID, PM) > 0 ? true : false;
        }

        #endregion

    }
}
